﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Web;

namespace UniversityStudentManagemnetSystem.DAO.Gateway
{
    public class ResetGateway:Gateway
    {
        
        public DataTable GetScheduleClass()
        {

            string selectQuery = string.Format("select * from t_ScheduleClass");
            Command.CommandText = selectQuery;
            DataTable table = null;
            try
            {
                DataSet dataSet = new DataSet();
                SqlDataAdapter adapter = new SqlDataAdapter(Command.CommandText, GetConnectionString());
                adapter.Fill(dataSet);
                table = dataSet.Tables[0];
                nbrOfTableRows = table.Rows.Count;
            }
           catch(FormatException exception)
                {
                    throw new Exception(exception.Message);
                }
            catch (Exception exception)
            {

                throw new Exception("System Error", exception);
            }
            finally
            {
                if (Connection != null && Connection.State == ConnectionState.Open)
                {
                    Connection.Close();
                }


            }
            return table;
        }

        public DataTable GetAllAssignCourse()
        {
            string selectQuery = string.Format("Select * from t_CourseAssignToTeacher");
            Command.CommandText = selectQuery;
            DataTable table = null;
            try
            {
                DataSet dataSet = new DataSet();
                SqlDataAdapter adapter = new SqlDataAdapter(Command.CommandText, GetConnectionString());
                adapter.Fill(dataSet);
                table = dataSet.Tables[0];
                nbrOfTableRows = table.Rows.Count;
            }
            catch (FormatException exception)
            {
                throw new Exception(exception.Message);
            }
            catch (Exception exception)
            {

                throw new Exception("System error", exception);
            }
            finally
            {
                if (Connection != null && Connection.State == ConnectionState.Open)
                {
                    Connection.Close();
                }

            }
            return table;
        }

        public bool Reset(DataTable classscheduleTable, DataTable assignTeacherTable)
        {
            string teacherAssignSelectQuery = string.Format(" select * from t_CourseAssignTeacherArk");
            string scheduleClassSelectQuery = string.Format(" select * from t_ScheduleClassArkive");
            DataTable table = null;
            SqlDataAdapter adapter;
            SqlCommandBuilder commandBuilder;
            DataSet dataSet;
            try
            {

                adapter=new SqlDataAdapter(teacherAssignSelectQuery,GetConnectionString());
                commandBuilder=new SqlCommandBuilder(adapter);
                dataSet=new DataSet();
                adapter.Fill(dataSet);
                table = dataSet.Tables[0];
                nbrOfTableRows = table.Rows.Count;
                foreach (DataRow  row in assignTeacherTable.Rows)
                {
                    int id = Convert.ToInt32(row["Id"]);
                    int teacherId = Convert.ToInt32(row["TeacherId"]);
                    int courseId = Convert.ToInt32(row["CourseId"]);
                    object[] objects = {id, teacherId, courseId};
                    table.Rows.Add(objects);
                    adapter.Update(dataSet);
                    dataSet.AcceptChanges();

                }


                adapter = new SqlDataAdapter(scheduleClassSelectQuery,GetConnectionString());
                commandBuilder=new SqlCommandBuilder(adapter);
                dataSet=new DataSet();
                adapter.Fill(dataSet);
                table = dataSet.Tables[0];
                nbrOfTableRows = table.Rows.Count;
                foreach (DataRow row in classscheduleTable.Rows)
                {
                    int id = Convert.ToInt32(row["Id"]);
                    int deptId = Convert.ToInt32(row["DepartmentId"]);
                    int semesterId = Convert.ToInt32(row["SemesterId"]);
                    int courseId = Convert.ToInt32(row["CourseId"]);
                    int buildingId = Convert.ToInt32(row["BuildingId"]);
                    int roomId = Convert.ToInt32(row["RoomId"]);
                    string day = row["Day"].ToString();
                    string flag = row["Flag"].ToString();
                    string starTime = row["StartingTime"].ToString();
                    string endTime = row["EndingTime"].ToString();
                    object[] objects = { id, deptId, semesterId, courseId, buildingId, roomId, day, flag, starTime, endTime };
                    table.Rows.Add(objects);
                    adapter.Update(dataSet);
                    dataSet.AcceptChanges();

                }


                Connection.Open();
                string deleteQueryTeacher = string.Format("DELETE FROM t_CourseAssignToTeacher");
                Command.CommandText = deleteQueryTeacher;
                Command.ExecuteNonQuery();
                string deleteQuerySchedule = string.Format("DELETE FROM t_ScheduleClass");
                Command.CommandText=deleteQuerySchedule;
                Command.ExecuteNonQuery();


            }
            catch (FormatException exception)
            {
                throw new Exception(exception.Message);
            }
            catch (Exception exception)
            {
                
                throw new Exception("System Error",exception);
            }
           
            finally
            {
                if (Connection!=null && Connection.State==ConnectionState.Open)
                {
                    Connection.Close();
                }
            }
            return true;
        }
    }
}